import pyodbc
from config import config


def execute_query(queryStatement):
    connection_string = (
        f"DRIVER={config.config_db['driver']};"
        f"SERVER={config.config_db['server']};"
        f"DATABASE={config.config_db['database']};"
        f"UID={config.config_db['username']};"
        f"PWD={config.config_db['password']}"
    )

    with pyodbc.connect(connection_string) as conn:
        with conn.cursor() as cursor:
            cursor.execute(queryStatement)
            results = cursor.fetchall()

            # 获取查询结果的列名
            columns = [column[0] for column in cursor.description]

            # 获取查询结果的所有行数据
            rows = cursor.fetchall()

            # 将查询结果转换成列表，每行数据作为一个字典
            results = [dict(zip(columns, row)) for row in rows]

            return results


def execute_statement(dml_statement):
    connection_string = (
        f"DRIVER={config.config_db['driver']};"
        f"SERVER={config.config_db['server']};"
        f"DATABASE={config.config_db['database']};"
        f"UID={config.config_db['username']};"
        f"PWD={config.config_db['password']}"
    )

    with pyodbc.connect(connection_string) as conn:
        with conn.cursor() as cursor:
            cursor.execute(dml_statement)
            conn.commit()

            return cursor.rowcount


def execute_procedure(statement):
    connection_string = (
        f"DRIVER={config.config_db['driver']};"
        f"SERVER={config.config_db['server']};"
        f"DATABASE={config.config_db['database']};"
        f"UID={config.config_db['username']};"
        f"PWD={config.config_db['password']}"
    )

    with pyodbc.connect(connection_string) as conn:
        with conn.cursor() as cursor:
            cursor.execute(f"SET NOCOUNT ON; {statement}")
            output_params = [param for param in cursor.parameters(
            ) if param.direction == pyodbc.SQL_PARAM_OUTPUT]
            cursor.execute(f"SET NOCOUNT OFF; {statement}")
            conn.commit()

            return_values = [param.value for param in output_params]

            return tuple(return_values) if len(return_values) > 1 else return_values[0]
